Hello and welcome back to Excel Tips and Tricks! This week, we have a Creator level post where we explore how to use different components of conditional formatting including data bars, colour scales and icon sets.
What is conditional formatting?
Conditional formatting is useful for creating dynamic and visually appealing spreadsheets. It can be helpful to highlight outliers and data, making your spreadsheets easier to read and understand.
You can find conditional formatting in the ‘Home’ tab in Excel under styles.
Data Bars
For example, I can use data bars to visualise the movement in sales when comparing prior year figures to current year.
To add this formatting, I select the data and under the conditional formatting menu select the ‘Data Bars’ option. From here, I can select the gradient. In my example, I selected the green data bar to show positive movements in green and negative movements in red.
Colour Scales
A simple example would be to apply a colour scale to my sales data to help visualise in a gradient the top performing stores in green to the lowest performing stores in red.
Icon Sets
To set this up, I select the data and under the conditional formatting menu select the ‘Icon Sets’ option. From here, I select the traffic lights option. This will apply the conditional formatting to my data based on a default threshold.
To do this, in the conditional formatting menu I click on ‘Manage Rules’. This will open the ‘Conditional Formatting Rules Manager’ where I can see all the rules applied to my worksheet.
- Excel Tips and Tricks #496 – ‘Check Performance’ in Excel
- Excel Tips and Tricks #495 - Excel “Tick”ery!
- Excel Tips & Tricks #494 - How to add a custom ribbon to your workbook part 3
- Excel Tips & Tricks #493 - How to add a custom ribbon to your workbook part 2
- Excel Tips & Tricks #492 - How to add a custom ribbon to your workbook
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.